from sys import maxsize

import orjson
from datetime import datetime
from fastapi.encoders import jsonable_encoder
from sqlalchemy.ext.associationproxy import association_proxy

from .common import *
from sqlalchemy import Integer, DECIMAL
from sqlalchemy import BigInteger, Boolean, CHAR, Column, Computed, DECIMAL, Date, DateTime, Float, ForeignKeyConstraint, Identity, Index, Integer, LargeBinary, Numeric, PrimaryKeyConstraint, SmallInteger, String, Table, Unicode, text
from sqlalchemy.dialects.mssql import DATETIME2, IMAGE, TINYINT


@add_serializer
class TemplateType(YgBase):
    __tablename__: str = "YG_TemplateType"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int_pk_auto]
    code: Mapped[required_unique_name]
    name: Mapped[required_unique_name]
    category: Mapped[required_string]
    templates: Mapped[List["Template"]] = relationship(back_populates="template_type")

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


@add_serializer
class Template(YgBase):
    __tablename__: str = "YG_Template"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int_pk_auto]
    name: Mapped[required_unique_name]
    description: Mapped[required_string] = mapped_column(String(1000), default='', nullable=True)
    config: Mapped[str] = mapped_column(String(4000), nullable=False)
    template_type_id: Mapped[int] = mapped_column(ForeignKey('YG_TemplateType.id'))
    template_type: Mapped[TemplateType] = relationship(lazy=False, back_populates="templates")

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


# 定义关联中间表，并有金额字段
@add_serializer
class CheckingBillFee(SqlBase):
    __tablename__: str = "YG_CheckingBill_fee"
    id_CheckingBill = mapped_column(Integer,ForeignKey('YG_CheckingBill.id'), primary_key=True)
    id_FeeType = mapped_column(Integer,ForeignKey('YG_FeeType.id'), primary_key=True)
    amount = mapped_column(DECIMAL(12, 2))
    checkingBill = relationship('CheckingBill', back_populates='checkingBillFees')
    yGFeeType = relationship('YGFeeType', back_populates='checkingBillFees')

""" 这种方式;中间表如果有数据获取比较困难;新增数据也困难-->改用代理表模式
CheckingBillFee = Table('YG_CheckingBill_fee', SqlBase.metadata,
 Column('id_CheckingBill', Integer, ForeignKey('YG_CheckingBill.id')),
    Column('id_FeeType', Integer, ForeignKey('YG_FeeType.id')),
    Column('amount', DECIMAL(12, 2))  # 新增的分数字段
)
"""

@add_serializer
class CheckingBill(YgBase):
    __tablename__: str = "YG_CheckingBill"
    id: Mapped[int_pk_auto]
    bill_date: Mapped[timestamp_default_now]
    bill_no: Mapped[str] = mapped_column(String(50))
    bill_type: Mapped[int]
    flight_no: Mapped[str] = mapped_column(String(50))
    departure_port: Mapped[str] = mapped_column(String(10))
    destination_port: Mapped[str] = mapped_column(String(10))
    status: Mapped[str] = mapped_column(String(10))
    articles: Mapped[int]
    weight: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    weight_for_fee: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    unit_price: Mapped[DECIMAL(12, 2)] = mapped_column(DECIMAL(12, 2), nullable=True)
    memo: Mapped[str]= mapped_column(Unicode(4000))
    total_amount: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    id_trade_partner: Mapped[int] = mapped_column(ForeignKey('YG_TradePartner.id'), nullable=True)
    id_company: Mapped[str]
    checkingBillFees:Mapped[List['CheckingBillFee']]  = relationship('CheckingBillFee', back_populates='checkingBill')
    yGFeeTypes:Mapped[List['YGFeeType']] = association_proxy('checkingBillFees', 'yGFeeType',creator=lambda yGFeeType: CheckingBillFee(yGFeeType=yGFeeType))
    # yGFeeTypes = relationship('YGFeeType', secondary=CheckingBillFee, back_populates='checkingBills') # old way

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.bill_no}, departure_port: {self.departure_port}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.bill_no}, departure_port: {self.departure_port}'


@add_serializer
class YGFeeType(YgBase):
    __tablename__ = 'YG_FeeType'
    id: Mapped[int_pk_auto]
    name = mapped_column(Unicode(100))
    code = mapped_column(String(50))
    checkingBillFees = relationship('CheckingBillFee', lazy=False, back_populates='yGFeeType')
    # checkingBills = association_proxy('checkingBillFees', 'checkingBill')
    amount = association_proxy('checkingBillFees', 'amount')
    # checkingBills = relationship('CheckingBill', secondary=CheckingBillFee, back_populates='yGFeeTypes') # old way

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


@add_serializer
class CheckingBillFromExcel(YgBase):
    __tablename__: str = "YG_CheckingBillFromExcel"
    bill_no: Mapped[int_pk_str]
    bill_date: Mapped[timestamp_default_now]
    id_CheckingBill: Mapped[int] = mapped_column(ForeignKey('YG_CheckingBill.id'),nullable=True)
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    file_name: Mapped[str]=mapped_column(String(64))
    weight_for_fee: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    total_amount: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    client: Mapped["Client"] = relationship(lazy=False, back_populates="checking_bill_from_excels")

    def __repr__(self) -> str:
        return f'name: {self.bill_no}'
    def __str__(self) -> str:
        return f'name: {self.bill_no}'


@add_serializer
class Client(YgBase):
    __tablename__ = 'YG_client'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='PK__YG_clien__3213E83FFEB3F042'),
    )
    id: Mapped[int_pk_auto]
    phone: Mapped[str] = mapped_column(String(20))
    email: Mapped[str] = mapped_column(String(50))
    addr: Mapped[str] = mapped_column(Unicode(100))
    memo: Mapped[str] = mapped_column(Unicode(4000))
    name: Mapped[str] = mapped_column(Unicode(100))
    profit: Mapped[str] = mapped_column(Numeric(20, 2))
    paid_amount: Mapped[str] = mapped_column(Numeric(20, 2))
    total: Mapped[int] = mapped_column(Integer)
    unpaid_amount = mapped_column(Numeric(21, 2), Computed('(isnull([profit],(0))-isnull([paid_amount],(0)))', persisted=False))
    income = mapped_column(Numeric(20, 2))
    expenditure = mapped_column(Numeric(20, 2))
    invoice_amount = mapped_column(DECIMAL(12, 2))
    checking_bill_from_excels: Mapped[List["CheckingBillFromExcel"]] = relationship(lazy=False, back_populates="client")

    def __repr__(self) -> str:
        return f'name: {self.name}'
    def __str__(self) -> str:
        return f'name: {self.name}'


@add_serializer
class BillCheckedRecord(YgBase):
    __tablename__ = 'YG_billChecked_record'
    id : Mapped[int_pk_auto]
    bill_no: Mapped[str]
    income = mapped_column(Numeric(20, 2))
    expenditure = mapped_column(Numeric(20, 2))
    profit = mapped_column(Numeric(20, 2))
    memo: Mapped[str] = mapped_column(Unicode(4000))
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    accounted_date = mapped_column(DATETIME2)
    id_company = mapped_column(Unicode(32))
    total = mapped_column(Integer)

    def __repr__(self) -> str:
        return f'name: {self.memo}'
    def __str__(self) -> str:
        return f'name: {self.memo}'


@add_serializer
class Payment(YgBase):
    __tablename__ = 'YG_payment'
    id : Mapped[int_pk_auto]
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    name = mapped_column(String(50,))
    amount = mapped_column(Numeric(20, 2))
    memo = mapped_column(Unicode(4000))
    payment_date = mapped_column(DATETIME2)
    id_invoice = mapped_column(String(250))
    bill_month = mapped_column(String(10))

    def __repr__(self) -> str:
        return f'name: {self.name}'
    def __str__(self) -> str:
        return f'name: {self.name}'


@add_serializer
class sys_depart(YgBase):
    __tablename__: str = "sys_depart"
    id: Mapped[str] = mapped_column(Unicode(32), primary_key=True, nullable=False)
    parent_id:Mapped[str] = mapped_column(Unicode(32), ForeignKey('sys_depart.id'), nullable=True)
    children: Mapped[list['sys_depart']] = relationship('sys_depart', back_populates='parent', remote_side=[id])
    parent: Mapped['sys_depart'] = relationship('sys_depart', back_populates='children', remote_side=[parent_id])
    depart_name: Mapped[str] = mapped_column( Unicode(100), nullable=False)
    depart_name_en: Mapped[str] = mapped_column(Unicode(500), nullable=False)
    depart_name_abbr: Mapped[str] = mapped_column(Unicode(500), nullable=False)
    depart_order: Mapped[int] = mapped_column(Integer)
    description: Mapped[str] = mapped_column(Unicode)
    org_category: Mapped[str] = mapped_column(Unicode(10))
    org_type: Mapped[str] = mapped_column(Unicode(10))
    org_code: Mapped[str] = mapped_column(Unicode(64))
    mobile: Mapped[str] = mapped_column(Unicode(32))
    fax: Mapped[str] = mapped_column(Unicode(32))
    address: Mapped[str] = mapped_column(Unicode(100))
    memo: Mapped[str] = mapped_column(Unicode(500))
    status: Mapped[str] = mapped_column(Unicode(1), nullable=False)
    depart_class: Mapped[str] = mapped_column(Unicode(1000))

    def __repr__(self) -> str:
        return f'name: {self.depart_name}'
    def __str__(self) -> str:
        return f'name: {self.depart_name}'


@add_serializer
class TradePartner(YgBase):
    __tablename__: str = "YG_TradePartner"
    id: Mapped[int_pk_auto]
    group_type: Mapped[str]
    fee_items: Mapped[str] = mapped_column(String(64))
    bill_type: Mapped[str] = mapped_column(String(64))
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    id_company: Mapped[int] = mapped_column(ForeignKey('sys_depart.id'), nullable=True)

    def __repr__(self) -> str:
        return f'name: {self.group_type}'
    def __str__(self) -> str:
        return f'name: {self.group_type}'